home *** CD-ROM | disk | FTP | other *** search
/ Agent Central Host Computer / Agent - Central Host Computer.iso / _SETUP.1 / vcbcount.sql < prev    next >
Text File  |  2000-05-12  |  3KB  |  71 lines
  1.  
  2. CREATE OR REPLACE VIEW VCBCOUNT AS 
  3. SELECT CASHBOXTOTALS.cashbox_glid, CASHBOXTOTALS.count_date,
  4.     BATCH.batch_seq,
  5.     SUBBATCH.coin_one cents_$, SUBBATCH.coin_two nickels_$,
  6.      SUBBATCH.coin_three dimes_$, SUBBATCH.coin_four quarters_$,
  7.      SUBBATCH.coin_five halfs_$, SUBBATCH.coin_six SBAs_$,
  8.     (SUBBATCH.coin_one + SUBBATCH.coin_two + SUBBATCH.coin_three +
  9.     SUBBATCH.coin_four + SUBBATCH.coin_five + SUBBATCH.coin_six ) coin_total,
  10.     SUBBATCH.coin_total_value,
  11.     subbatch.coin_seven * 100 sm_tokens, 
  12.       subbatch.coin_eight * 100 lg_tokens, 
  13.     SUBBATCH.bill_one ones_$, SUBBATCH.bill_two fives_$,
  14.      SUBBATCH.bill_three tens_$, SUBBATCH.bill_four twenties_$,
  15.     (SUBBATCH.bill_one + SUBBATCH.bill_two + SUBBATCH.bill_three +
  16.      SUBBATCH.bill_four ) bill_total,
  17.     SUBBATCH.receipt_total,
  18.     (SUBBATCH.coin_one + SUBBATCH.coin_two + SUBBATCH.coin_three +
  19.     SUBBATCH.coin_four + SUBBATCH.coin_five + SUBBATCH.coin_six )
  20.      + (SUBBATCH.bill_one + SUBBATCH.bill_two + SUBBATCH.bill_three +
  21.      SUBBATCH.bill_four ) total_before_adjust,
  22.     CASHBOXTOTALS.grand_total total,
  23.     vcbadjust.cb_adjust adjust,
  24.     vbatchadjust.batch_adjust,
  25.     vdayadjust.day_adjust
  26. FROM SUBBATCH,
  27.     CASHBOXTOTALS, /*cashbox,*/
  28.     BATCH,
  29.     BATCHTOTALS,
  30.     ACCTDAYTOTALS ,
  31.     vcbadjust,
  32.     vbatchadjust,
  33.     vdayadjust
  34. WHERE 
  35.  SUBBATCH.mrc_batch_id = BATCH.mrc_batch_id 
  36. AND  SUBBATCH.subbatch_seq = CASHBOXTOTALS.cashbox_total_seq
  37. AND BATCH.batch_seq = BATCHTOTALS.batch_total_seq_num
  38. AND BATCHTOTALS.acctday_total_seq_num = ACCTDAYTOTALS.acctday_total_seq_num
  39. AND vcbadjust.cashbox_total_seq = CASHBOXTOTALS.cashbox_total_seq
  40. AND vbatchadjust.batch_total_seq_num  = BATCHTOTALS.batch_total_seq_num
  41. AND vdayadjust.acctday_total_seq_num  = ACCTDAYTOTALS.acctday_total_seq_num
  42. UNION
  43. SELECT CASHBOXTOTALS.cashbox_glid, CASHBOXTOTALS.count_date,
  44.     0 batch_seq,
  45.     SUBBATCH.coin_one cents_$, SUBBATCH.coin_two nickels_$,
  46.      SUBBATCH.coin_three dimes_$, SUBBATCH.coin_four quarters_$,
  47.      SUBBATCH.coin_five halfs_$, SUBBATCH.coin_six SBAs_$,
  48.     (SUBBATCH.coin_one + SUBBATCH.coin_two + SUBBATCH.coin_three +
  49.     SUBBATCH.coin_four + SUBBATCH.coin_five + SUBBATCH.coin_six ) coin_total,
  50.     SUBBATCH.coin_total_value,
  51.     subbatch.coin_seven * 100 sm_tokens, 
  52.       subbatch.coin_eight * 100 lg_tokens, 
  53.     SUBBATCH.bill_one ones_$, SUBBATCH.bill_two fives_$,
  54.      SUBBATCH.bill_three tens_$, SUBBATCH.bill_four twenties_$,
  55.     (SUBBATCH.bill_one + SUBBATCH.bill_two + SUBBATCH.bill_three +
  56.      SUBBATCH.bill_four ) bill_total,
  57.     SUBBATCH.receipt_total,
  58.     (SUBBATCH.coin_one + SUBBATCH.coin_two + SUBBATCH.coin_three +
  59.     SUBBATCH.coin_four + SUBBATCH.coin_five + SUBBATCH.coin_six )
  60.      + (SUBBATCH.bill_one + SUBBATCH.bill_two + SUBBATCH.bill_three +
  61.      SUBBATCH.bill_four ) total_before_adjust,
  62.     CASHBOXTOTALS.grand_total total,
  63.     vcbadjust.cb_adjust adjust, 
  64.     0 batch_adjust,
  65.     0 day_adjust
  66. FROM SUBBATCH,
  67.     CASHBOXTOTALS, 
  68.     vcbadjust
  69. WHERE SUBBATCH.mrc_batch_id = 0
  70. AND  SUBBATCH.subbatch_seq = CASHBOXTOTALS.cashbox_total_seq
  71. AND vcbadjust.cashbox_total_seq = CASHBOXTOTALS.cashbox_total_seq;